package flights.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import flights.bi.Ticket;;

public class TicketHandler {

	public static List<Ticket> getAllTicketsByBooking(int nBookingId) throws SQLException
	{
		List<Ticket> Tickets = new ArrayList<Ticket>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM ticket WHERE booking_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nBookingId);
			ResultSet rs = st.executeQuery();
			Tickets = extractTicketsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return Tickets;
	}
	
	
	
	public static Ticket getTicketByID(int nticketID) throws SQLException
	{
		List<Ticket> Tickets = new ArrayList<Ticket>();
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM ticket where Ticket_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nticketID);
			ResultSet rs = st.executeQuery();
			Tickets = extractTicketsFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		if (Tickets.size() != 0){
			return Tickets.get(0);			
		}
		else
		{
			return null;
		}
	}
	
	public static int deleteBTicketByFlight(int nFlightId) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM ticket WHERE seat_id IN (SELECT fs.seat_id FROM flight_seat fs WHERE fs.flight_id = ?);";
		int nRows = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlightId);
			nRows = st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	public static int deleteBTicketByID(int nTicketID) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM ticket where Ticket_id=?";
		int nRows = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nTicketID);
			nRows = st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	public static int deleteByBooking(int nBookingID) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM ticket where booking_id = ?";
		int nRows = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nBookingID);
			nRows = st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;
	}
	
	public static int upsertTicket(Ticket ticket) throws SQLException
	{
		List<Ticket> singleTicket = new ArrayList<Ticket>();
		singleTicket.add(ticket);
		int nRetVal = upsertTicket(singleTicket);
		return nRetVal;
	}
	
	public static int upsertTicket(List<Ticket> tickets) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		int nRowsAffected = 0;
		for (int i = 0; i < tickets.size(); i++) {
			if (getTicketByID(tickets.get(i).get_ticketId()) != null)
			{
				String sql = "update ticket set booking_id = ? , passport_number =? , first_name=?,last_name =?,seat_id =?,conn_prev_ticket_id =? where ticket_id = ?";
				try (PreparedStatement st = conn.prepareStatement(sql)) {
					st.clearParameters();
					st.setInt(1,tickets.get(i).getBooking_id());
					st.setString(2,tickets.get(i).getPassport_number());
					st.setString(3, tickets.get(i).getFirst_name());
					st.setString(4, tickets.get(i).getLast_name());
					st.setInt(5, tickets.get(i).getSeat_id());
					st.setInt(6, tickets.get(i).getPrev_ticket());
					st.setInt(7, tickets.get(i).get_ticketId());
					nRowsAffected += st.executeUpdate();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
			else // new Ticket
			{
				String sql = "insert into ticket(booking_id,passport_number,first_name,last_name,seat_id,conn_prev_ticket_id) values (?,?,?,?,?,?)";
				try (PreparedStatement st = conn.prepareStatement(sql)) {
					st.clearParameters();
					st.setInt(1,tickets.get(i).getBooking_id());
					st.setString(2,tickets.get(i).getPassport_number());
					st.setString(3, tickets.get(i).getFirst_name());
					st.setString(4, tickets.get(i).getLast_name());
					st.setInt(5, tickets.get(i).getSeat_id());
					if (tickets.get(i).getPrev_ticket() == null)
					{
						st.setNull(6, java.sql.Types.INTEGER);
					}
					else
					{
						st.setInt(6, tickets.get(i).getPrev_ticket());
					}
					nRowsAffected += st.executeUpdate();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
		}
		
		conn.close();
		return nRowsAffected;
	}
	
	private static List<Ticket> extractTicketsFromRS(ResultSet resultSet) throws SQLException {
		List<Ticket> Tickets = new ArrayList<Ticket>();
		
		while (resultSet.next()) {
			int ticket_id=resultSet.getInt("ticket_id");
			String passport_number=resultSet.getString("passport_number");
			String first_name=resultSet.getString("first_name");
			String last_name=resultSet.getString("last_name");
			int seat_id=resultSet.getInt("seat_id");
			int prev_ticket_id=resultSet.getInt("conn_prev_ticket_id");
			int booking_id=resultSet.getInt("booking_id");
			
			Ticket ticket = new Ticket(ticket_id, passport_number, first_name, last_name, seat_id, prev_ticket_id, booking_id);
			Tickets.add(ticket);
		}
		
		return Tickets;
	}



	public static int deleteTicketBySeat(int nFlight_SeatID) throws SQLException 
	{
		Connection conn = DBConn.createConnection();
		String sql = "DELETE FROM ticket where seat_id = ?";
		int nRows = 0;
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setInt(1,nFlight_SeatID);
			nRows = st.executeUpdate();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return nRows;		
	}
	
}
